package dbh_helper;

require Exporter;


our @ISA = qw(Exporter);
our $VERSION = 1.00;
our @EXPORT = qw(dbh_get_single_value dbh_get_nth_value dbh_get_median);



use strict;
use warnings;
use DBI;
use DBD::Pg;
use Carp;


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
#
#
#	DBI queries
#
#		Wrapper to obtain a value or the median from a database table
#
#



#_________________________________________________________________________________________

#	dbh_get_median

#_________________________________________________________________________________________
sub dbh_get_single_value($$)
{
	my ($dbh, $sql_txt) = @_;
	my @results;
	eval
	{
		@results = $dbh->selectrow_array($sql_txt);
	};
	if ($@ || defined $dbh->err)
	{
		croak "Error:\n\t".$dbh->errstr."\n\t[$sql_txt]\n";
	}
	return $results[0];
}

sub dbh_get_nth_value($$$$)
{
	my ($dbh, $n_pos, $table, $var) = @_;
	return dbh_get_single_value($dbh, "SELECT $var FROM $table ORDER BY $var ".
										"OFFSET $n_pos LIMIT 1");
}
sub dbh_get_median($$$)
{
	my ($dbh, $table, $var) = @_;
	my $sql = "SELECT count(*) FROM $table ";
    if ($table =~ /where/i)
	{
		$table .= " AND NOT $var is null";
	}
	else
	{
		$table .= " WHERE NOT $var is null";
	}
	my $total_cnt = dbh_get_single_value($dbh, "SELECT count(*) FROM $table");
	my $median_pos	= int($total_cnt / 2);
	my $lower_pos	= int($total_cnt * 0.166666666667);
	my $upper_pos	= int($total_cnt * 0.833333333333);
	my $median		= dbh_get_nth_value($dbh, $median_pos, $table, $var);
	my $lower		= dbh_get_nth_value($dbh, $lower_pos, $table, $var);
	my $upper		= dbh_get_nth_value($dbh, $upper_pos, $table, $var);
	return ($lower, $median, $upper, $total_cnt);
}
#
#
#	DBI queries
#
#		Wrapper to obtain a value or the median from a database table
#
#
#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888



return 1;
